import os
import re
import tempfile
from io import BytesIO
from typing import Any, List, Dict, Tuple

import pandas as pd
from flask import Blueprint, request, jsonify, send_file
from openpyxl import Workbook
from openpyxl.styles import Alignment, Font, Side, Border
from openpyxl.utils import column_index_from_string, get_column_letter
from openpyxl.worksheet.page import PageMargins
from openpyxl.worksheet.worksheet import Worksheet
from werkzeug.datastructures import FileStorage

from app.db.crud.teacher_management_query import CourseQuery, UserQuery, user_query, course_query
from app.db.model.teacherManagementModels import CourseSchedule
from app.decorators import require_params
from app.utils.exceptions import MissingParamError, InternalServerError, ResourceNotFoundError, InvalidRoleValueError
from app.utils.str_util import remove_trailing_parentheses

course = Blueprint('course', __name__, url_prefix='/course')

weekday_to_col = {
    "schedule1": 4,
    "schedule2": 5,
    "schedule3": 6,
    "schedule4": 7,
    "schedule5": 8,
}

period_to_row = {
    1: 5,
    2: 6,
    3: 7,
    4: 8,
    5: 9,
    6: 10,
    7: 11,
    8: 12,
    9: 13,
    10: 14
}

excel_headers = [
    "学期", "年级", "班级", "在校人数", "专业(代码)", "升学方式", "对接院校",
    "课程代码", "课程名称", "课程类型", "周课时", "教学周数", "考核方式",
    "负责教材征订系部", "课程所属系部", "任课教师", "班主任", "校区",
    "周一", "周二", "周三", "周四", "周五", "周六", "周日",
    "备注1",
    "书名", "第一主编", "出版单位", "ISBN", "版次", "版次日期",
    "教材性质", "教材来源", "教材类型", "价格", "课程性质",
    "是否首次选用", "首次选用时间", "已使用年限", "备注2"
]

# 上面excel列名对应的数据库字段名
db_columns = [
    "semester", "grade", "class_name", "student_amount", "major", "enrollment_method", "parner_institution",
    "course_id", "course_name", "course_type", "weekly_hour", "teaching_week", "assessment_method",
    "book_department", "course_department", "instructor", "class_advisor", "campus",
    "schedule1", "schedule2", "schedule3", "schedule4", "schedule5", "schedule6", "schedule7",
    "remark1",
    "book_name", "first_editor_in_chief", "publishing_unit", "isbn", "release", "release_date",
    "book_nature", "book_source", "book_type", "price", "course_nature",
    "is_select_first", "select_first_date", "used_year", "remark2"
]


@course.route('/arrangement', methods=["GET"])
# @login_required
# @role_required('all_teacher')
@require_params('teacherId', 'semester')
def get_arrangement_by_teacher() -> Any:
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")

        teacher_id = params.get("teacherId")
        semester = params.get("semester")
        teacher_name = user_query.get_name_by_teacher_id(teacher_id).strip()

        data = course_query.get_arrangement_by_teacher(teacher_name, semester)
        records = []
        for row in data:
            if '、' in row['instructor']:
                for teacher in str(row['instructor']).split('、'):
                    if remove_trailing_parentheses(teacher) == teacher_name:
                        records.append(row)
                        break
            else:
                if row['instructor'] == teacher_name:
                    records.append(row)
        return jsonify({
            "code": 200,
            "message": "success",
            "data": records
        })
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@course.route('/semester', methods=["GET"])
# @login_required
# @role_required('all_teacher')
@require_params('teacherId')
def get_semester_by_teacher() -> Any:
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")
        teacher_id = params.get("teacherId")
        teacher_name = user_query.get_name_by_teacher_id(teacher_id).strip()
        data = course_query.get_semester_by_teacher(teacher_name)
        return jsonify({
            "code": 200,
            "message": "success",
            "data": data
        })
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@course.route('/remove', methods=["DELETE"])
# @login_required
# @role_required('all_teacher')
@require_params('semester')
def remove_course_arrangement() -> Any:
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")
        semester = params.get("semester")
        removed = course_query.remove_course_arrangement(semester)
        return jsonify({
            "code": 200,
            "message": "success",
            "data": removed
        })
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@course.route('/generate/schedule/template', methods=["GET"])
# @login_required
# @role_required('all_teacher')
@require_params()
def template_for_import_schedule():
    try:
        wb = Workbook()
        ws = wb.active
        ws.title = "课表导入模板（必须遵守！）"

        ws["A1"] = "课表导入模板"
        ws["A1"].font = Font(size=30)
        ws["A1"].alignment = Alignment(horizontal="center", vertical="center")

        ws["A2"] = "为了规范学校的数据，请各位教师们严格按照上面的规范填写数据"
        ws["A2"].font = Font(size=8)
        ws["A2"].alignment = Alignment(horizontal="center", vertical="center")

        required_labels = [
            "必填", "必填", "必填", "非必填", "必填", "非必填", "非必填",
            "必填", "必填", "必填", "必填", "必填", "必填",
            "非必填", "必填", "必填", "非必填", "必填",
            "非必填", "非必填", "非必填", "非必填", "非必填", "非必填", "非必填",
            "非必填",
            "非必填", "非必填", "非必填", "非必填", "非必填", "非必填",
            "非必填", "非必填", "非必填", "非必填", "非必填",
            "必填", "非必填", "非必填", "非必填"
        ]

        for col_idx, (header, label) in enumerate(zip(excel_headers, required_labels), start=1):

            cell3 = ws.cell(row=3, column=col_idx, value=header)
            cell3.font = Font(size=18)
            cell3.alignment = Alignment(horizontal="center", vertical="center")

            cell4 = ws.cell(row=4, column=col_idx, value=label)
            font_kwargs = {"size": 12}
            if label == "必填":
                font_kwargs["bold"] = True
            cell4.font = Font(**font_kwargs)
            cell4.alignment = Alignment(horizontal="center", vertical="center")

        max_col = len(excel_headers)
        ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=max_col)
        ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=max_col)

        total_cols = len(excel_headers)
        for col_idx in range(1, total_cols + 1):
            col_letter = get_column_letter(col_idx)
            max_length = 0

            for row in range(1, 5):
                cell_value = ws.cell(row=row, column=col_idx).value
                if cell_value is not None:
                    length = len(str(cell_value))
                    if length > max_length:
                        max_length = length
            adjusted_width = min(max_length + 20, 30)
            ws.column_dimensions[col_letter].width = adjusted_width

        output = BytesIO()
        wb.save(output)
        output.seek(0)
        return send_file(
            output,
            as_attachment=True,
            download_name="课表导入模板.xlsx"
        )

    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@course.route('/import/excel/schedule', methods=["POST"])
# @login_required
# @role_required('all_teacher')
@require_params('file', include_files=True)
def import_schedule():
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")
        file_one: FileStorage = params.get('file')
        if file_one.filename == '' or file_one.filename is None:
            raise ResourceNotFoundError({"u-facingMsg": "文件名异常！"})
        if not file_one.filename.endswith('.xlsx'):
            raise ResourceNotFoundError({"u-facingMsg": "文件名格式异常！"})
        # 保存到临时文件，避免内存过大
        with tempfile.NamedTemporaryFile(delete=False, suffix='.xlsx') as tmp:
            file_one.save(tmp.name)
            tmp_path = tmp.name

        df = pd.read_excel(
            tmp_path,
            header=2,
            skiprows=[3],
            names=excel_headers,
            dtype=str,
            keep_default_na=True,
            na_values=["", "NULL", "null", "N/A"],
        )
        # 删除临时文件
        os.unlink(tmp_path)
        # 验证列是否匹配
        if list(df.columns) != excel_headers:
            raise ResourceNotFoundError({"u-facingMsg": "文件格式异常！"})
        # 5. 类型转换：周课时应为数值（decimal）
        if "周课时" in df.columns:
            df["周课时"] = pd.to_numeric(df["周课时"], errors='coerce')  # 无法转的变 NaN
        # 6. 将 NaN 转为 None（SQLAlchemy 会转为 NULL）
        df = df.where(pd.notnull(df), None)
        # 7. 转为字典列表（每行一个 dict）
        records = df.to_dict('records')

        if not records:
            raise ResourceNotFoundError({"u-facingMsg": "资源不存在！"})
        # 8. 批量插入数据库（每批1000行）
        batch_size = 1000
        course_objects: List[CourseSchedule] = []
        for row in records:
            param = {}
            for db_col, excel_col in zip(db_columns, excel_headers):
                param[db_col] = row[excel_col]
            course_obj = CourseSchedule(**param)
            course_objects.append(course_obj)

        for i in range(0, len(course_objects), batch_size):
            batch: List[CourseSchedule] = course_objects[i:i + batch_size]
            course_query.import_course_schedule(batch)

        return jsonify({
            "code": 200,
            "message": "success",
            "data": "添加数据成功"
        })
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@course.route('/export/instructor/schedule', methods=["GET"])
# @login_required
# @role_required('all_teacher')
@require_params('semester', 'instructor')
def export_instructor_schedule():
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")
        semester = params.get("semester")
        instructor = params.get("instructor")
        side_thick = Side(
            style="thick",
            color="000000",
        )
        flag_cells = [[0 for _ in range(5)] for _ in range(10)]

        if not instructor or not course_query.is_instructor_exist(semester, instructor):
            raise ResourceNotFoundError(extra_details={"u-facingMsg": "教师名称错误"})

        data = course_query.get_timetable_by_semester_and_instructor(semester, instructor)

        courses = normalize_schedule(data)

        wb, ws = fill_out_instructor_doc(semester, instructor)

        for cs in courses:
            for schedule in ["schedule1", "schedule2", "schedule3", "schedule4", "schedule5"]:
                if cs.get(schedule) is None or cs.get(schedule) == '':
                    continue
                elif "18:00-19:20" in cs.get(schedule) or "19:40-21:00" in cs.get(
                        schedule) or "19:30-21:00" in cs.get(schedule):
                    ws.cell(period_to_row[10],
                            weekday_to_col[schedule],
                            value=f"{cs.get('course_name')}\n{cs[schedule]}\n({cs.get('class_name')})")
                else:
                    split_course_and_week = cs.get(schedule).split("(")
                    if len(split_course_and_week) > 1:
                        split_course_and_week[-1] = "(" + split_course_and_week[-1]

                    parts = str(split_course_and_week[0]).split(",")
                    for part in parts:
                        nums = part.split("-")

                        if len(nums) == 1:

                            ws.cell(period_to_row[int(nums[0])], weekday_to_col[schedule],
                                    value=f"{cs.get('course_name')} \n({cs.get('class_name')})")
                        else:
                            start = int(nums[0])
                            end = int(nums[-1])
                            min_row = period_to_row[start]
                            min_col = weekday_to_col[schedule]
                            max_row = period_to_row[end]
                            max_col = min_col

                            ws.merge_cells(start_row=min_row,
                                           start_column=min_col,
                                           end_row=max_row,
                                           end_column=min_col)
                            if len(split_course_and_week) > 1:
                                value = cs.get("course_name") + split_course_and_week[-1]
                            else:
                                value = cs.get("course_name")
                            ws.cell(row=period_to_row[start], column=weekday_to_col[schedule],
                                    value=f"{value} \n({cs.get('class_name')})")

                            for row in range(min_row, max_row + 1):
                                for col in range(min_col, max_col + 1):
                                    cell = ws.cell(row=row, column=col)
                                    flag_cells[row - 5][col - 4] = 1
                                    if row == min_row:
                                        set_border(cell, top=side_thick)
                                    if row == max_row:
                                        set_border(cell, bottom=side_thick)
                                    if col == min_col:
                                        set_border(cell, left=side_thick)
                                    if col == max_col:
                                        set_border(cell, right=side_thick)

        for row in range(period_to_row[1], period_to_row[10] + 1):
            for col in range(weekday_to_col["schedule1"], weekday_to_col["schedule5"] + 1):
                if flag_cells[row - 5][col - 4] != 1:
                    cell = ws.cell(row=row, column=col)
                    cell.border = Border(top=side_thick,
                                         bottom=side_thick,
                                         left=side_thick,
                                         right=side_thick)

        output = BytesIO()
        wb.save(output)
        output.seek(0)

        return send_file(
            output,
            as_attachment=True,
            download_name=semester + "_" + instructor + "_" + "课表.xlsx"
        )
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@course.route('/export/class/schedule', methods=["GET"])
# @login_required
# @role_required('all_teacher')
@require_params('semester', 'class_name')
def export_class_schedule():
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")
        semester = params.get("semester")
        class_name = params.get("class_name")
        side_thick = Side(
            style="thick",
            color="000000",
        )
        flag_cells = [[0 for _ in range(5)] for _ in range(10)]

        if not class_name or not course_query.is_classname_exist(semester, class_name):
            raise ResourceNotFoundError(extra_details={"u-facingMsg": "班级名称错误"})
        data = course_query.get_timetable_by_semester_and_class_name(semester, class_name)

        courses = normalize_schedule(data)

        class_advisor = courses[0].get("class_advisor")
        wb, ws = fill_out_class_doc(semester, class_name, class_advisor)

        for cs in courses:
            for schedule in ["schedule1", "schedule2", "schedule3", "schedule4", "schedule5"]:
                if cs.get(schedule) is None or cs.get(schedule) == '':
                    continue
                elif "18:00-19:20" in cs.get(schedule) or "19:40-21:00" in cs.get(
                        schedule) or "19:30-21:00" in cs.get(schedule):
                    ws.cell(period_to_row[10],
                            weekday_to_col[schedule],
                            value=f"{cs.get('course_name')} \n{cs[schedule]} \n{cs.get('instructor')})")
                else:
                    split_course_and_week = cs.get(schedule).split("(")
                    if len(split_course_and_week) > 1:
                        split_course_and_week[-1] = "(" + split_course_and_week[-1]

                    parts = str(split_course_and_week[0]).split(",")
                    for part in parts:
                        nums = part.split("-")

                        if len(nums) == 1:
                            if len(split_course_and_week) > 1:
                                value = cs.get("course_name") + split_course_and_week[-1]
                            else:
                                value = cs.get("course_name")
                            ws.cell(period_to_row[int(nums[0])], weekday_to_col[schedule],
                                    value=f"{value} \n({cs.get('instructor')})")
                        else:
                            start = int(nums[0])
                            end = int(nums[-1])
                            min_row = period_to_row[start]
                            min_col = weekday_to_col[schedule]
                            max_row = period_to_row[end]
                            max_col = min_col

                            ws.merge_cells(start_row=period_to_row[start],
                                           start_column=weekday_to_col[schedule],
                                           end_row=period_to_row[end],
                                           end_column=weekday_to_col[schedule])
                            if len(split_course_and_week) > 1:
                                value = cs.get("course_name") + split_course_and_week[-1]
                            else:
                                value = cs.get("course_name")
                            ws.cell(row=period_to_row[start], column=weekday_to_col[schedule],
                                    value=f"{value} \n({cs.get('instructor')})")

                            for row in range(min_row, max_row + 1):
                                for col in range(min_col, max_col + 1):
                                    cell = ws.cell(row=row, column=col)
                                    flag_cells[row - 5][col - 4] = 1
                                    if row == min_row:
                                        set_border(cell, top=side_thick)
                                    if row == max_row:
                                        set_border(cell, bottom=side_thick)
                                    if col == min_col:
                                        set_border(cell, left=side_thick)
                                    if col == max_col:
                                        set_border(cell, right=side_thick)

        for row in range(period_to_row[1], period_to_row[10] + 1):
            for col in range(weekday_to_col["schedule1"], weekday_to_col["schedule5"] + 1):
                if flag_cells[row - 5][col - 4] != 1:
                    cell = ws.cell(row=row, column=col)
                    cell.border = Border(top=side_thick,
                                         bottom=side_thick,
                                         left=side_thick,
                                         right=side_thick)

        output = BytesIO()
        wb.save(output)
        output.seek(0)

        return send_file(
            output,
            as_attachment=True,
            download_name=semester + "_" + class_name + "_" + "课表.xlsx"
        )
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@course.route('/info/get', methods=["GET"])
# @login_required
# @role_required('all_teacher')
@require_params()
def get_course_info():
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.args
        else:
            raise MissingParamError("vp")
        course_id = params.get("id", '')
        name = params.get("name", '')
        code = params.get("code", '')
        course_type = params.get("course_type", '')
        course_class = params.get("course_class", '')
        course_depart = params.get("course_depart", '')
        course_hour = params.get("course_hour", '')
        note = params.get("note", '')

        # 获取分页参数，设置默认值
        page = int(params.get("page", 1))
        page_size = int(params.get("pageSize", 14))

        # 验证分页参数
        if page < 1 and page != 0:
            page = 1
        if page_size < 1 and page_size != 0:
            page_size = 14
        if page_size > 100:  # 限制最大页大小
            page_size = 100
        data, total_count = course_query.get_course_info(course_id, name, code, course_type, course_class,
                                                         course_depart,
                                                         course_hour, note, page, page_size)
        return jsonify({
            "code": 200,
            "message": "success",
            "data": data,
            "total": total_count
        })
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@course.route('/info/update', methods=["PUT"])
# @login_required
# @role_required('all_teacher')
@require_params("role", "id")
def update_course_info():
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
            request_data: dict = request.form.to_dict() or {}
        else:
            raise MissingParamError("vp")
        role = params.get("role")
        if role not in ["admin"]:
            raise InvalidRoleValueError({"u-facingMsg": "角色异常！"})
        course_id = params.get("id", '')
        if not course_id:
            raise ResourceNotFoundError(extra_details={"u-facingMsg": "课程信息错误"})
        name = request_data.get("name", '')
        code = request_data.get("code", '')
        course_type = request_data.get("course_type", '')
        course_class = request_data.get("course_class", '')
        course_depart = request_data.get("course_depart", '')
        course_hour = request_data.get("course_hour", '')
        note = request_data.get("note", '')
        course_query.update_course_info(int(course_id), name, code, course_type, course_class, course_depart,
                                        course_hour, note)
        print("update:::course_id ", course_id, "name ", name, "code ", code, "note ", note)
        return jsonify({
            "code": 200,
            "message": "success",
            "data": "更新课程信息成功"
        })
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@course.route('/info/add', methods=["POST"])
# @login_required
# @role_required('all_teacher')
@require_params("role", "name", "code", "course_type", "course_class", "course_depart", "course_hour", "note")
def add_course_info():
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")
        role = params.get("role")
        if role not in ["admin"]:
            raise InvalidRoleValueError({"u-facingMsg": "角色异常！"})
        name = params.get("name", '')
        code = params.get("code", '')
        course_type = params.get("course_type", '')
        course_class = params.get("course_class", '')
        course_depart = params.get("course_depart", '')
        course_hour = params.get("course_hour", '')
        note = params.get("note", '')
        course_query.add_course_info(name, code, course_type, course_class, course_depart, course_hour, note)
        return jsonify({
            "code": 200,
            "message": "success",
            "data": "添加课程信息成功"
        })
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@course.route('/info/delete', methods=["DELETE"])
# @login_required
# @role_required('all_teacher')
@require_params("role", "ids")
def delete_course_info():
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")
        role = params.get("role")
        if role not in ["admin"]:
            raise InvalidRoleValueError({"u-facingMsg": "角色异常！"})
        ids = params.get("ids", '')
        if not ids:
            raise ResourceNotFoundError(extra_details={"u-facingMsg": "课程信息错误"})
        if ',' in ids:
            course_id: List[int] = [int(id_str.strip()) for id_str in ids.split(',') if id_str]
        else:
            course_id = [int(ids)]
        course_query.delete_course_info(course_id)
        return jsonify({
            "code": 200,
            "message": "success",
            "data": "删除课程信息成功"
        })
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


@course.route('/info/export', methods=["GET"])
# @login_required
# @role_required('all_teacher')
@require_params("role")
def export_course_info():
    try:
        if hasattr(request, 'validated_params'):
            params: dict = request.validated_params
        else:
            raise MissingParamError("vp")
        role = params.get("role")
        if role not in ["admin", "department_head"]:
            raise InvalidRoleValueError({"u-facingMsg": "角色异常！"})
        # 查询数据，由于数据较少不需要按条件查询了，直接全部查出来
        result = course_query.get_course_info('', '', '', '', '', '', '', '', 0, 0)
        data = result[0] if isinstance(result, tuple) and len(result) > 0 else result
        # 将数据写入Excel文件
        wb = Workbook()
        ws: Worksheet = wb.active
        center_alignment = Alignment(horizontal="center", vertical="center")
        thin_side = Side(border_style="thin", color="000000")
        thin_border = Border(left=thin_side, right=thin_side, top=thin_side, bottom=thin_side)
        songti_font = Font(name="宋体", size=10)

        ws.column_dimensions['A'].width = 20
        ws.column_dimensions['B'].width = 30
        ws.column_dimensions['C'].width = 20
        ws.column_dimensions['D'].width = 25
        ws.column_dimensions['E'].width = 20
        ws.column_dimensions['F'].width = 20
        ws.column_dimensions['G'].width = 25
        ws.column_dimensions['H'].width = 60
        ws.title = "课程代码原始总表"
        list_field = ["序号", "课程名称", "课程编号", "课程类型", "课程类别", "开课单位", "学时", "备注"]
        for i in range(1, 9):
            cell = ws.cell(row=1, column=i, value=list_field[i - 1])
            cell.alignment = center_alignment
            cell.font = songti_font
        # 填充每行数据
        for i, row in enumerate(data, start=2):
            for col, value in enumerate([
                row.get("id"),
                row.get("name"),
                row.get("code"),
                row.get("course_type", ""),
                row.get("course_class", ""),
                row.get("course_depart", ""),
                row.get("course_hour", ""),
                row.get("note", "")
            ], start=1):
                cell = ws.cell(row=i, column=col, value=value)
                cell.alignment = center_alignment
                cell.border = thin_border
                cell.font = songti_font

        output = BytesIO()
        wb.save(output)
        output.seek(0)
        return send_file(
            output,
            as_attachment=True,
            download_name=f"课程代码原始总表.xlsx"
        )
    except Exception as e:
        user_facing_msg = (
            e.details.get('u-facingMsg')
            if hasattr(e, 'details') and isinstance(e.details, dict) and e.details.get('u-facingMsg', None)
            else "服务器错误"
        )
        raise InternalServerError({"u-facingMsg": user_facing_msg}) from e


def style_cell(cell, font=None, align=True):
    default_font = Font(name='楷体', size=16)
    center_align = Alignment(horizontal='center', vertical='center')
    if font:
        cell.font = font
    else:
        cell.font = default_font
    if align:
        cell.alignment = center_align


def fill_out_class_doc(semester: str, class_name: str, class_advisor: str) -> Tuple[Workbook, Worksheet]:
    wb = Workbook()
    ws: Worksheet = wb.active
    ws.title = f"{semester}-{class_name}课表"

    ws.column_dimensions["A"].width = 6
    ws.column_dimensions["B"].width = 2
    ws.column_dimensions["C"].width = 2

    ws.row_dimensions[1].height = 50
    ws.row_dimensions[2].height = 15
    for i in range(3, 15):
        ws.row_dimensions[i].height = 35
    ws.column_dimensions["D"].width = 23
    ws.column_dimensions["E"].width = 23
    ws.column_dimensions["F"].width = 23
    ws.column_dimensions["G"].width = 23
    ws.column_dimensions["H"].width = 23

    ws.merge_cells("A1:H1")
    cell = ws["A1"]
    cell.value = "班级课程表"
    style_cell(cell, Font(name="楷体", size=30))

    ws.merge_cells("A2:H2")
    cell = ws["A2"]
    ws.row_dimensions[2].height = 15
    cell.alignment = Alignment(horizontal='center', vertical='center')
    cell.value = "正德、笃学、强技、致远"

    ws.merge_cells("A3:G3")
    cell = ws["A3"]
    cell.value = semester
    style_cell(cell, Font(name="楷体", size=12))

    cell = ws["H3"]
    cell.value = f"班主任:{class_advisor}"
    style_cell(cell, Font(name="楷体", size=12))

    headers = ["", "", ""] + ["星期一", "星期二", "星期三", "星期四", "星期五"]
    ws.append(headers)
    for col in range(4, 9):
        style_cell(ws.cell(4, col), Font(name="楷体", size=12))
    ws.merge_cells("A4:C4")
    cell = ws["A4"]
    cell.value = "节次"
    cell.alignment = Alignment(horizontal='center', vertical='center')

    ws.merge_cells("A5:A8")
    cell = ws["A5"]
    cell.value = "上午"
    style_cell(cell, Font(name="等线", size=12))

    ws.merge_cells("A9:A13")
    cell = ws["A9"]
    cell.value = "下午"
    style_cell(cell, Font(name="等线", size=12))

    cell = ws["A14"]
    cell.value = "晚上"
    style_cell(cell, Font(name="等线", size=12))

    for i in range(5, 15):
        ws.merge_cells(start_row=i, start_column=2, end_row=i, end_column=3)
        cell = ws.cell(i, 2)
        cell.value = i - 4
        style_cell(cell, Font(name="楷体", size=12))

    for row in ws.iter_rows(min_row=5, max_row=14,
                            min_col=column_index_from_string('D'), max_col=column_index_from_string('H')):
        for cell in row:
            cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)

    ws.merge_cells("A15:H15")
    cell = ws["A15"]
    ws.row_dimensions[15].heigh = 15
    cell.value = "愿师者明德以教，学子日新为学，教学相长，共臻于善。"
    cell.alignment = Alignment(horizontal='center', vertical='center')
    set_public_border(ws)
    set_landscape_a4(ws)
    return wb, ws


def fill_out_instructor_doc(semester: str, instructor: str) -> Tuple[Workbook, Worksheet]:
    wb = Workbook()
    ws: Worksheet = wb.active
    ws.title = f"{semester}-{instructor}课表"

    ws.column_dimensions["A"].width = 6
    ws.column_dimensions["B"].width = 2
    ws.column_dimensions["C"].width = 2

    ws.row_dimensions[1].height = 50
    ws.row_dimensions[2].height = 15
    for i in range(3, 15):
        ws.row_dimensions[i].height = 35
    ws.column_dimensions["D"].width = 23
    ws.column_dimensions["E"].width = 23
    ws.column_dimensions["F"].width = 23
    ws.column_dimensions["G"].width = 23
    ws.column_dimensions["H"].width = 23

    ws.merge_cells("A1:H1")
    cell = ws["A1"]
    cell.value = "教师课程表"
    style_cell(cell, Font(name="楷体", size=30))

    ws.merge_cells("A2:H2")
    cell = ws["A2"]
    ws.row_dimensions[2].height = 15
    cell.alignment = Alignment(horizontal='center', vertical='center')
    cell.value = "正德、笃学、强技、致远"

    ws.merge_cells("A3:G3")
    cell = ws["A3"]
    cell.value = semester
    style_cell(cell, Font(name="楷体", size=12))

    cell = ws["H3"]
    cell.value = f"教师:{instructor}"
    style_cell(cell, Font(name="楷体", size=12))

    headers = ["", "", ""] + ["星期一", "星期二", "星期三", "星期四", "星期五"]
    ws.append(headers)
    for col in range(4, 9):
        style_cell(ws.cell(4, col), Font(name="楷体", size=12))
    ws.merge_cells("A4:C4")
    cell = ws["A4"]
    cell.value = "节次"
    cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)

    ws.merge_cells("A5:A8")
    cell = ws["A5"]
    cell.value = "上午"
    style_cell(cell, Font(name="等线", size=12))

    ws.merge_cells("A9:A13")
    cell = ws["A9"]
    cell.value = "下午"
    style_cell(cell, Font(name="等线", size=12))

    cell = ws["A14"]
    cell.value = "晚上"
    style_cell(cell, Font(name="等线", size=12))

    for i in range(5, 15):
        ws.merge_cells(start_row=i, start_column=2, end_row=i, end_column=3)
        cell = ws.cell(i, 2)
        cell.value = i - 4
        style_cell(cell, Font(name="楷体", size=12))

    for row in ws.iter_rows(min_row=5, max_row=14,
                            min_col=column_index_from_string('D'), max_col=column_index_from_string('H')):
        for cell in row:
            cell.alignment = Alignment(horizontal='center', vertical='center')

    ws.merge_cells("A15:H15")
    cell = ws["A15"]
    ws.row_dimensions[15].heigh = 15
    cell.value = "秉明明德之志，承新民之任。祝新学期工作顺利，止于至善！"
    cell.alignment = Alignment(horizontal='center', vertical='center')

    set_public_border(ws)

    set_landscape_a4(ws)

    return wb, ws


def set_public_border(ws: Worksheet):
    side_thick = Side(style="thick", color="000000")

    min_row, min_col, max_row, max_col = 3, 1, 15, 8

    for row in range(min_row, max_row + 1):
        for col in range(min_col, max_col + 1):
            cell = ws.cell(row=row, column=col)

            border = Border()

            if row == min_row:
                border = Border(top=side_thick,
                                left=border.left, right=border.right, bottom=border.bottom)
            if row == max_row:
                border = Border(bottom=side_thick,
                                top=border.top, left=border.left, right=border.right)
            if col == min_col:
                border = Border(left=side_thick,
                                top=border.top, right=border.right, bottom=border.bottom)
            if col == max_col:
                border = Border(right=side_thick,
                                top=border.top, left=border.left, bottom=border.bottom)

            if row == 4:
                border = Border(top=side_thick, bottom=side_thick,
                                left=border.left, right=border.right)

            if (col == 1 or col == 3) and (5 <= row < 15):
                border = Border(right=side_thick,
                                top=border.top, left=border.left, bottom=border.bottom)
            if 2 <= col <= 3 and 5 <= row < 15:
                border = Border(bottom=side_thick,
                                top=border.top, left=border.left, right=border.right)
            if row in [8, 13] and col == 1:
                border = Border(bottom=side_thick,
                                top=border.top, left=border.left, right=border.right)

            if row == 15:
                border = Border(top=side_thick,
                                left=border.left, right=border.right, bottom=border.bottom)

            cell.border = border


def set_landscape_a4(ws: Worksheet):
    ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE

    ws.page_setup.paperSize = ws.PAPERSIZE_A4

    ws.print_area = "A1:H15"

    ws.page_setup.fitToWidth = 1
    ws.page_setup.fitToHeight = 1

    ws.page_margins = PageMargins(
        left=0.5,
        right=0.5,
        top=0.5,
        bottom=0.5,
        header=0.3,
        footer=0.3
    )


def normalize_string(value: str) -> str:
    if value is None:
        return ""
    s = value.strip()

    s = s.replace("，", ",")
    s = s.replace("（", "(")
    s = s.replace("）", ")")
    s = s.replace("：", ":")
    s = s.replace("～", "-").replace("——", "-").replace("－", "-")

    s = re.sub(r"\s+", "", s)
    return s


def normalize_schedule(data: List[Dict]) -> List[Dict]:
    normalize_records = data
    if not data:
        return []
    for row in normalize_records:
        for schedule in ["schedule1", "schedule2", "schedule3", "schedule4", "schedule5"]:
            row[schedule] = normalize_string(row[schedule])
    return normalize_records


def set_border(cell, top=None, bottom=None, left=None, right=None):
    """在 cell 已有边框的基础上叠加新的边框"""
    border = cell.border

    cell.border = Border(
        top=top if top else border.top,
        bottom=bottom if bottom else border.bottom,
        left=left if left else border.left,
        right=right if right else border.right
    )
